package com.azt.utils;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URL;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

	public static final byte CELL_STRING = 1;
	public static final byte CELL_INT = 2;
	public static final byte CELL_FLOAT = 3;
	public static final byte CELL_DOUBLE = 4;
	public static final byte CELL_LONG = 5;
	public static final byte CELL_PIC = 6;

	public static Object getCellValue(HSSFCell cell, ExcelColumn def) {
		byte type = def.getType();
		switch (type) {
		case CELL_STRING:
			return getStringCellValue(cell);
		case CELL_INT:
			return getIntCellValue(cell);
		case CELL_FLOAT:
			return getFloatCellValue(cell);
		case CELL_DOUBLE:
			return getDoubleCellValue(cell);
		case CELL_LONG:
			return getLongCellValue(cell);
		}
		return null;
	}

	public static String getStringCellValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			return StringUtils.trimToNull(cell.getStringCellValue());
		}
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			DecimalFormat df = new DecimalFormat("#");
			String s = df.format(cell.getNumericCellValue());
			return s;
		}
		if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
			return null;
		}
		throw new RuntimeException("Failed to find string cell value: "
				+ cell.getCellType());
	}

	public static Number getNumericCellValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		Object object = null;
		if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC || cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
			object = cell.getNumericCellValue();
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
			object = StringUtils.trimToNull(cell.getStringCellValue());
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
			return null;
		}
		return FormatUtil.parseNumber(object);
	}

	public static int getIntCellValue(HSSFCell cell) {
		Number number = getNumericCellValue(cell);
		return number == null ? 0 : number.intValue();
	}

	public static float getFloatCellValue(HSSFCell cell) {
		Number number = getNumericCellValue(cell);
		return number == null ? 0f : number.floatValue();
	}

	public static double getDoubleCellValue(Cell cell) {
		Number number = getNumericCellValue(cell);
		return number == null ? 0d : number.doubleValue();
	}

	public static long getLongCellValue(HSSFCell cell) {
		Number number = getNumericCellValue(cell);
		return number == null ? 0L : number.longValue();
	}

	public static Map<String, List<PictureData>> getPictures(HSSFSheet sheet) {
		Map<String, List<PictureData>> picMap = new HashMap<String, List<PictureData>>();
		List<HSSFPictureData> pictures = sheet.getWorkbook().getAllPictures();
		if (pictures.isEmpty()) {
			return picMap;
		}
		if (sheet.getDrawingPatriarch() == null) {
			return picMap;
		}
		for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
			HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
			if (shape instanceof HSSFPicture) {
				HSSFPicture pic = (HSSFPicture) shape;
				int pictureIndex = pic.getPictureIndex() - 1;
				HSSFPictureData picData = pictures.get(pictureIndex);
				String cellKey = String.valueOf(anchor.getRow1()) + "_"
						+ String.valueOf(anchor.getCol1());
				List<PictureData> cellPics = picMap.get(cellKey);
				if (cellPics == null) {
					cellPics = new LinkedList<PictureData>();
					picMap.put(cellKey, cellPics);
				}
				cellPics.add(picData);
			}
		}
		return picMap;
	}

	private static int findCellIndex(HSSFRow row, String cellName)
			throws ExcelException {
		int firstCell = row.getFirstCellNum();
		int lastCell = row.getLastCellNum();
		for (int i = firstCell; i <= lastCell; i++) {
			HSSFCell cell = row.getCell(i);
			if (cell == null) {
				continue;
			}
			String title = StringUtils.trimToEmpty(getStringCellValue(cell));
			if (title.equals(cellName)) {
				return i;
			}
		}
		throw new ExcelException(ExcelException.ERROR_COULD_NOT_FIND_COLUMN,
				cellName);
	}

	public static HSSFWorkbook getWorkbook(InputStream in)
			throws ExcelException {
		HSSFWorkbook wb = null;
		// POIFSFileSystem fs = null;
		try {
			// fs = new POIFSFileSystem(in);
			wb = new HSSFWorkbook(in);
		} catch (IOException e) {
			throw new ExcelException(ExcelException.ERROR_NOT_AN_EXCEL_FILE);
		} finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {

                }
            }
		}
		return wb;
	}


	public static HSSFWorkbook getWorkbook(File file) throws ExcelException {
		try {
			return getWorkbook(new FileInputStream(file));
		} catch (FileNotFoundException e) {
			throw new ExcelException(ExcelException.ERROR_NOT_AN_EXCEL_FILE);
		}
	}
	
	public static Workbook getWorkbookCompatible(String filePath) throws Exception {

		URL url = new URL(filePath);

		InputStream stream = url.openStream();
		Workbook wb = null;
		try {
			// 2003
			wb = new HSSFWorkbook(stream);
		} catch (Exception e) {
			stream.close();
			// 2007
			try {
				stream = url.openStream();
				wb = new XSSFWorkbook(stream);
			} catch (Exception e1) {
				
			}
		}
		
		return wb;
	}

	
	
	public static List<String> readHeader(HSSFSheet sheet) {
		HSSFRow row = sheet.getRow(0);
		int firstCell = row.getFirstCellNum();
		int lastCell = row.getLastCellNum();
		List<String> keys = new ArrayList<String>(lastCell - firstCell + 1);
		for (int i = firstCell; i <= lastCell; i++) {
			HSSFCell cell = row.getCell(i);
			if (cell != null) {
				keys.add(getStringCellValue(cell));
			}
		}
		return keys;
	}

	public static <M> List<M> read(File file, ExcelColumn[] columns,
			RowParser<M> parser) throws ExcelException, Exception {
		HSSFWorkbook wb = getWorkbook(file);
		HSSFSheet sheet = wb.getSheetAt(0);
		return read(sheet, columns, parser);
	}

	public static <M> List<M> read(HSSFSheet sheet, ExcelColumn[] columns,
			RowParser<M> parser) throws ExcelException, Exception {

		int firstRow = sheet.getFirstRowNum();
		int lastRow = sheet.getLastRowNum();
		if (firstRow != 0) {
			throw new ExcelException(ExcelException.ERROR_DATA_IS_EMPTY);
		}
		HSSFRow titleRow = sheet.getRow(firstRow);
		int[] colIndexes = new int[columns.length];
		for (int i = 0; i < columns.length; i++) {
			colIndexes[i] = findCellIndex(titleRow, columns[i].getTitle());
		}

		List<M> list = new LinkedList<M>();

		for (int i = firstRow + 1; i <= lastRow; i++) {
			HSSFRow row = sheet.getRow(i);
			if (row == null) {
				continue;
			}

			Map<String, Object> item = new HashMap<String, Object>();

			for (int k = 0; k < colIndexes.length; k++) {
				HSSFCell cell = row.getCell(colIndexes[k]);
				if (cell == null) {
					continue;
				}
				ExcelColumn column = columns[k];
				Object value = null;
				switch (column.getType()) {
				case CELL_STRING:
					value = getStringCellValue(cell);
					break;
				case CELL_INT:
					value = getIntCellValue(cell);
					break;
				case CELL_FLOAT:
					value = getFloatCellValue(cell);
					break;
				case CELL_DOUBLE:
					value = getDoubleCellValue(cell);
					break;
				case CELL_LONG:
					value = getLongCellValue(cell);
					break;
				default:
					throw new ExcelException(
							ExcelException.ERROR_UNKNOWN_CELL_TYPE,
							column.getType());
				}
				item.put(column.getKey(), value);
			}

			M object = parser.parse(item, list, i, colIndexes);
			if (object == null) {
				continue;
			}
			list.add(object);

		}

		return list;
	}

	public static <D> void export(HSSFWorkbook wb,HSSFSheet sheet, List<Map<String,Object>> titles,
			Iterable<D> iter, RowRenderer<D> renderer) {

		int rowIndex = 0;

		/*
		HSSFRow titleRow = sheet.createRow(rowIndex);
		
		for (int i = 0; i < titles.length; i++) {
			titleRow.createCell(i).setCellValue(titles[i]);
		}
		if(titles!=null&&titles.size()>0){
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setFillForegroundColor(HSSFColor.RED.index);    //设置颜色为红色
	        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			for(int i=0;i< titles.size();i++){
				Map<String,Object> titleMap = titles.get(i);
				String title = (String)titleMap.get("title");
				boolean red = (Boolean)titleMap.get("red");
				HSSFCell cell = titleRow.createCell(i);
				cell.setCellValue(title);
				if(red){
					cell.setCellStyle(cellStyle);
				}
			}
		}*/
	    createCellTitles(wb, sheet, titles);


		for (D item : iter) {
			HSSFRow row = sheet.createRow(++rowIndex);
			row.setRowStyle(wb.createCellStyle());
			renderer.render(row, item);
		}
	}

	/**
	 * 提供精确的乘法运算。
	 *
	 * @param v1 被乘数
	 * @param v2 乘数
	 * @return 两个参数的积
	 */

	public static double mul(double v1, double v2) {
		BigDecimal b1 = new BigDecimal(Double.toString(v1));
		BigDecimal b2 = new BigDecimal(Double.toString(v2));
		return b1.multiply(b2).doubleValue();
	}
	
	/**
	 * 创建Excel表头
	 * @param wb
	 * @param sheet
	 * @param titles{title:标题名称,red:是否标红}
	 */
	public static <D> void createCellTitles(HSSFWorkbook wb,HSSFSheet sheet, List<Map<String,Object>> titles) {
		int rowIndex = 0;
		HSSFRow titleRow = sheet.createRow(rowIndex);
		if(titles!=null&&titles.size()>0){
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setFillForegroundColor(HSSFColor.RED.index);    //设置颜色为红色
	        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			for(int i=0;i< titles.size();i++){
				Map<String,Object> titleMap = titles.get(i);
				String title = (String)titleMap.get("title");
				HSSFCell cell = titleRow.createCell(i);
				cell.setCellValue(title);
				if(titleMap.get("red")!=null){
					boolean red = (Boolean)titleMap.get("red");
					if(red){
						cell.setCellStyle(cellStyle);
					}
				}
			}
		}
	}

	public static class ExcelColumn {
		private String key;
		private String title;
		private byte type;

		public ExcelColumn(String key, String title, byte type) {
			super();
			this.key = key;
			this.title = title == null ? key : title;
			this.type = type;
		}

		public String getKey() {
			return key;
		}

		public String getTitle() {
			return title;
		}

		public byte getType() {
			return type;
		}

	}

	public static interface RowRenderer<R> {
		void render(HSSFRow row, R item);
	}

	public static interface RowParser<P> {
		P parse(Map<String, Object> data, List<P> datas, int rowIndex,
                int[] columnIndexes) throws Exception;
	}

	@SuppressWarnings("serial")
	public static class ExcelException extends Exception {
		public static final int ERROR_NOT_AN_EXCEL_FILE = 1;
		public static final int ERROR_DATA_IS_EMPTY = 2;
		public static final int ERROR_COULD_NOT_FIND_COLUMN = 3;
		public static final int ERROR_UNKNOWN_CELL_TYPE = 4;

		private int errorCode;
		private Object[] errorParams;

		public ExcelException(int errorCode) {
			super();
			this.errorCode = errorCode;
		}

		public ExcelException(int errorCode, Object... errorParams) {
			super();
			this.errorCode = errorCode;
			this.errorParams = errorParams;
		}

		public int getErrorCode() {
			return errorCode;
		}

		public Object[] getErrorParams() {
			return errorParams;
		}

	}
	
	
	public static <D> void export(String sheetName,List<Map<String,Object>> titles,Iterable<D> iter,
			RowRenderer<D> renderer,HttpServletResponse response , HttpServletRequest request)
			throws Exception {
		createExcel(sheetName,titles,iter,renderer,response,request);
		response.getOutputStream().flush();
	}
	
	public static <D> void export(String[] sheetNames,List<Map<String,Object>> titles,HttpServletResponse response) throws Exception {
		createExcel(sheetNames,titles,response);
		response.getOutputStream().flush();
		
	}
	

	public static <D> void createExcel(String sheetName,List<Map<String,Object>> titles,Iterable<D> iter,
			RowRenderer<D> renderer,HttpServletResponse response , HttpServletRequest request) throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFSheet sheet = createSheet(wb, sheetName, 1);
		if(null != sheetName && "采购询价商品清单".equals(sheetName)){// 下载的询价清单需要保护工作簿 2016-10-10
			sheet.protectSheet("azt365");
		}
		export(wb,sheet, titles, iter, renderer);
		if(sheetName.contains("询价商品清单")){//在下载询价单时增加提示
			sheet.addMergedRegion(new CellRangeAddress(sheet.getLastRowNum() + 1, sheet.getLastRowNum() + 1, 0, 7));
			HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
			Font font = wb.createFont();
			HSSFCellStyle cellStyle = wb.createCellStyle();
			font.setColor(HSSFColor.RED.index);
			HSSFCell cell = row.createCell(0);
			cellStyle.setFont(font);
			cell.setCellStyle(cellStyle);
			cell.setCellValue("红色背景列为必填项。不报价的产品不用填写");
			sheet.setColumnWidth(0, 10 * 512);
			sheet.setColumnWidth(1, 10 * 512);
			sheet.setColumnWidth(2, 10 * 512);
			sheet.setColumnWidth(3, 10 * 512);
			sheet.setColumnWidth(8, 20 * 512);
		}
		try {
			String fileName = getFileName(sheetName);
			initResponse(response, fileName, request.getHeader("User-Agent"));// 初始化response
			OutputStream os = response.getOutputStream();
			wb.write(os);
		} catch (Exception ex) {
			throw ex;
		} finally {

		}
	}
	
	public static <D> void createExcel(String[] sheetNames,List<Map<String,Object>> titles,HttpServletResponse response) throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		if(sheetNames!=null&&sheetNames.length>0){
			for(String sheetName:sheetNames){
				HSSFSheet sheet = createSheet(wb, sheetName, 1);
				createCellTitles(wb, sheet, titles);
			}
			try {
				String fileName = getFileName("询价商品清单");
				initResponse(response, fileName, null);// 初始化response
				OutputStream os = response.getOutputStream();
				wb.write(os);
				os.flush();
				os.close();
			} catch (Exception ex) {
				throw ex;
			} finally {
				
			}
		}
	}
	
	private static final int EXCEL_MAX_ROW_COUNT = 65536;
	/**
	 * 创建一个Sheet页并返回该对象
	 * 
	 * @param wb
	 *            HSSFWorkbook
	 * @param seq
	 *            int
	 * @return HSSFSheet
	 */
	private static HSSFSheet createSheet(HSSFWorkbook wb, String sheetName, int seq) {
		int sub = (seq - 1) * EXCEL_MAX_ROW_COUNT + 1;
		if (sub < 1) {
			sub = 1;
		}
		if (seq > 1) {
			sheetName += "(" + seq + ")";
		}
		return wb.createSheet(sheetName);
	}

	public static void initResponse(HttpServletResponse response, String fileName, String userAgent) throws UnsupportedEncodingException{
//		response.setContentType("application/vnd.ms-excel;");
//		if (StringUtils.isNotEmpty(userAgent)) {
//			if (userAgent.toLowerCase().indexOf("msie") > 0 || userAgent.toLowerCase().indexOf(""edge) > 0) {
//				fileName = URLEncoder.encode(fileName, "UTF-8");
//			} else {
//				fileName = new String(fileName.getBytes("UTF-8"), "iso8859-1");
//			}
//		} else {
//			// 按原来的默认做法
//			fileName = new String(fileName.getBytes("UTF-8"), "iso8859-1");
//		}
//		if (fileName.indexOf(".xls") == -1) {
//			fileName = fileName + ".xls";
//		}
		response.setHeader("Content-Disposition", "attachment; filename=" + CommonUtil.toUtf8String(userAgent ,fileName));
	}
	
	private static String getFileName(String tableName) {
		SimpleDateFormat form = new SimpleDateFormat("yyyy-MM-dd");
		return tableName + form.format(new java.util.Date().getTime()) + ".xls";
	}

}
